package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;


public class DBProduct implements IFDBProduct{
    private  Connection con;
    /** Creates a new instance 
     * of DBEmployee */
    public DBProduct() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all products
    public ArrayList<Product> getAllProducts()
    {
        return miscWhere("");
    }
    //get one product having the productID
    public Product findProduct(int productID)
    {   String wClause = "  productID = " + productID ;
        return singleWhere(wClause);
    }
    //get one product having the productID
    public Product searchProduct(int productID)
    {
    	String wClause = "productID = '" + productID + "'";
    	System.out.println("Search Product " + wClause);
    	return singleWhere(wClause);
    }
    //find one product having the product name
    public Product searchProductPname(String attValue)
    {
        String wClause = "pname like '%" + attValue + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause);
    }
      //find one product having the minStock
    public ArrayList<Product> searchProductMinStock(int attValue)
    {
    	String wClause = "minStock = '" + attValue + "'";
    	System.out.println("Search Products: " + wClause);
    	return miscWhere(wClause);
    }
    // find one Product having the curStock
    public ArrayList<Product> searchProductCurStock(int attValue)
    {
    	String wClause = "curStock = '" + attValue + "'";
    	System.out.println("Search Products: " + wClause);
    	return miscWhere(wClause);
    }
     //insert a new product
    public void insertProduct(Product product) throws SQLException
    {  //call to get the next productID number
        int nextProdID = GetMax.getMaxId("Select max(productID) from Product");
        nextProdID = nextProdID + 1;
        System.out.println("next productID = " +  nextProdID);
  
	   String query="INSERT INTO product(productID, pname, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, cvr, curStock)  VALUES('"+
			   nextProdID   + "','"  +
		    product.getPName()   + "','"  +
            product.getPurchasePrice()   + "','"  +
		    product.getSalesPrice() + "','"  +
            product.getRentPrice() + "','"  +
		    product.getCountryOfOrigin() + "','"  +
            product.getMinStock() + "','" +
		    product.getCvr() + "','" + 
            product.getCurStock() + "')";
                     
           
      System.out.println("insert : " + query);		  					
      try{ // insert new product
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(SQLException ex){
	 	  	System.out.println("Insert exception in product db: "+ex);
	   }
	}
	
	public void updateProduct(Product product) throws SQLException
	{
		Product productObj  = product;
	  
		String query="UPDATE product SET "+
		 	  "pname ='"+ productObj.getPName()+"', "+
			  "purchasePrice ='"+ productObj.getPurchasePrice()+"', "+
		 	  "salesPrice ='"+ productObj.getSalesPrice()+"', "+
			  "rentPrice ='"+ productObj.getRentPrice()+"', "+
		 	  "countryOfOrigin ='"+ productObj.getCountryOfOrigin()+"', "+
			  "minStock ='" + productObj.getMinStock()+"', "+
		 	  "cvr ='"+ productObj.getCvr()+"', "+
		 	  "curStock ='" + productObj.getCurStock() + "'" +
		          " WHERE productID = '"+ productObj.getProductID() + "'";
                System.out.println("Update query:" + query);
  		try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//slut try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
	}
	
	public void deleteProduct(int productID) throws SQLException
	{
	  
	  	String query="DELETE FROM product WHERE productID = " +productID;
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
	}
	
	//private methods
        //michWere is used whenever we want to select more than one product
	
	 
	private ArrayList<Product> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
		     	 Product productObj = new Product();
			 productObj = buildProduct(results);	
                         list.add(productObj);	
			}//end while
                       
			stmt.close();
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one product
	private Product singleWhere(String wClause)
	{
		ResultSet results;
		Product productObj = new Product();
        productObj.setProductID(-1);
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            productObj = buildProduct(results);
                            //association is to be build
                            stmt.close();
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return productObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an customer object
	private Product buildProduct(ResultSet results)
      {   Product productObj = new Product();
          try{
                productObj.setProductID(results.getInt(1));
                productObj.setPName(results.getString(2));
                productObj.setPurchasePrice(results.getDouble(3));
                productObj.setSalesPrice(results.getDouble(4));
	  	        productObj.setRentPrice(results.getDouble(5));
	        	productObj.setCountryOfOrigin(results.getString(6));
	        	productObj.setMinStock(results.getInt(7));
	        	productObj.setCvr(results.getString(8));
	        	productObj.setCurStock(results.getInt(9));
          }
         catch(Exception e)
         {
             System.out.println("error in building the product object");
         }
         return productObj;
      }
}  